package org.mysql;

//JDBC-Include
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

//Vector class
import java.util.Vector;


public class MySQL {

	/**
	 * Connection handle
	 */
	Connection conn = null;

	/**
	 * Statement handle
	 */
	Statement stmt = null;

	/**
	 * ResultSet handle
	 */
	ResultSet rs = null;
	
	/**
	 * Prepared Statement
	 */
	PreparedStatement prstmt = null;
	
	/**
	 * Last inserted key
	 */
	int lastInsertedID = -1;

	/*
	 * Database connection parameter
	 */
	String driver = null;
	String server = null;
	String database = null;
	String user = null;
	String password = null;

	/**
	 * Constructor
	 * @param driver e.g. 'mysql'
	 * @param server e.g. 'localhost'
	 * @param database e.g. 'myDatabase'
	 * @param user e.g. 'root'
	 * @param pass e.g. 'myPass'
	 */
	public MySQL()
	{
		this.driver		= "mysql";
		this.server		= "localhost";
		this.database	= "mindstorm_db";
		this.user		= "mindstorm";
		this.password	= "mindstorm";

		this.connect();
	}
	
	private static MySQL instance = null;
	
    public static MySQL getInstance() {
        if (instance == null) {
            instance = new MySQL();
        }
        return instance;
    }


	/**
	 * Connects to a database
	 */
	private void connect() {
		try {
			this.conn =
			DriverManager.getConnection("jdbc:"+this.driver+"://"+this.server+"/"+ this.database +"?" +
			"user="+this.user+"&password="+this.password);
		} catch (SQLException ex) {
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
	}

	/**
	 * Kills the connection to a database
	 */
	private void close() {
		try {
			this.conn.close();
		} catch(SQLException ex) {
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
	}

	/**
	 * Returns the sum of columns returned by the last SELECT-Query
	 * @return Sum of columns
	 */
	public int getColumnCount() {
		// ResultSetMetaData handle
		ResultSetMetaData meta = null;

		// Sum of columns
		int cols = 0;

		try
		{
			// GetMetaData from ResultSet
			meta = this.rs.getMetaData();
		} catch(SQLException ex) {
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}

		try
		{
			// GetColumnCount
			cols = meta.getColumnCount();
		} catch(SQLException ex) {
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}

		return cols;
	}

	/**
	 * Returns the sum of rows returned by the last SELECT-Query
	 * @return Sum of rows
	 */
	public int getRowCount() {

		// Sum of columns
		int rows = 0;

		// Save RowPointer
		int tempRowPointer = 0;

		try
		{
			// Save the current row pointer
			tempRowPointer = this.rs.getRow();

			// Set the pointer to the last row
			this.rs.last();

			// Save the last row number (which equals the sum of rows)
			rows = this.rs.getRow();

			// Set the pointer to the saved pointer-position
			if(tempRowPointer != 0)
				this.rs.absolute(tempRowPointer);
			else
				this.rs.first();

		} catch(SQLException ex) {
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}

		return rows;
	}

	/**
	 * Sends a query to a database
	 * @param query Expects a String with a SQL-command (SELECT, DELETE, DROP, INSERT, ...)
	 * @return Returns a ResultSet if the query was a 'SELECT'.<br />
	 * If not it'll return 'NULL'
	 *
	 * TODO: Check if it's a "SELECT"-command; if it is: return null
	 */
	public void query(String query) {
		try {
		    this.stmt = this.conn.createStatement();
		    this.rs = this.stmt.executeQuery(query);
		}
		catch (SQLException ex){
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}
	}

	public void exec(String query)
	{
		try {
			this.prstmt = this.conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
			this.prstmt.executeUpdate();
			
			
		}
		catch (SQLException ex) {
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());			
		}
	}
	
	public int getLastInsertedID()
	{
		try {
			this.rs = prstmt.getGeneratedKeys();
			this.rs.next();
			this.lastInsertedID = rs.getInt(1);
		}
		catch(SQLException ex)
		{
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}
		
		return this.lastInsertedID;
	}
	
	public String[] getNextRow()
	{
		String[] row = new String[this.getColumnCount()];
		
		// Get next row
		try {
			this.rs.next();
		} catch (SQLException ex) {
			// handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}
		
		for(int i=1; i<=this.getColumnCount(); i++) 
		{
			try {
				row[i-1] = this.rs.getString(i);
			}
			catch(SQLException ex)
			{
				// handle any errors
			    System.out.println("SQLException: " + ex.getMessage());
			    System.out.println("SQLState: " + ex.getSQLState());
			    System.out.println("VendorError: " + ex.getErrorCode());
			}
			
		}
			
		return row;
	}
	
	/**
	 * Converts a ResultSet, taken from the last done query, to a Vector < Vector< String > >
	 * @return Returns a Vector < Vector < String > > which is a copy of the ResultSet
	 */
	public Vector < Vector<String> > resultSetToVector() {
		Vector < Vector<String> > table = new Vector < Vector<String> >();
		Vector<String> row = new Vector<String>();
		int colCount = this.getColumnCount();

		try {
			this.rs.beforeFirst();
			
			while(this.rs.next()) {
				// Iterate through all columns per row
				for(int i=1; i<=colCount; i++) {
					// Save the content
					row.add(this.rs.getString(i));
				}

				// Add VectorRow in VectorTable
				table.add(row);

				// Reset row
				row = null;
				row = new Vector<String>();
			}

			// Rowpointer reset
			this.rs.first();
		}
		catch (SQLException ex){
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}

		return table;
	}

	/**
	 * Destructor
	 */
	protected void finalize() {
		this.close();
	}

}
